package viper.main;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;


/**
 * @author Kevin Sin
 * 
 * Revision number: 1
 * Date created: 11/17/10
 * Date of Last Back-up on SVN: none
 * 
 * <p>This is a test for connecting to a MySQL database from Java.  It successfully
 * printed the two entries in the test MySQL table.
 */
public class MySQLFunctions {
	
	private static final String CONNECTION_NAME = 
		"jdbc:mysql://fling.seas.upenn.edu/?user=wksin&password=mysql4kev";
	private static final String DATABASE_NAME = "wksin";
	private static final String TABLE_NAME = "viper";
	private static final String IP_ADDR_COLUMN_NAME = "ip_addr";
	private static final String USERNAME_COLUMN_NAME = "username";
	private static final String STATUS_COLUMN_NAME = "status";
	public static final int STATUS_FREE = 1;
	public static final int STATUS_BUSY = 2;
	
	/**
	 * @return Connection if successful, null otherwise
	 */
	private static Connection getConnection() {
		Connection conn = null;
		try {
			conn = DriverManager.getConnection(CONNECTION_NAME);
		} catch(SQLException ex){
			System.out.println(ex.getMessage());
		}
		return conn;
	}
	
	/**
	 * @param conn - Connection
	 * @return Statement if successful, null otherwise
	 */
	private static Statement getStatment(Connection conn) {
		Statement stmt = null;
		try {
			stmt = conn.createStatement();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return stmt;
	}
	
	/**
	 * @param conn - Connection to be closed
	 * @param stmt - Statement to be closed
	 */
	private static void closeConnectionAndStatement(Connection conn, Statement stmt) {
		if (stmt != null){
			try {
				stmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			conn = null;
		}
	}
	
	/**
	 * @return an ArrayList of Object arrays.  Each Object array represents
	 * a row in the MySQL table for each user online.
	 */
	public static ArrayList<Object[]> getCurrentUsers() {
		ArrayList<Object[]> userlist = new ArrayList<Object[]>();
		Connection conn = getConnection();
		Statement stmt = getStatment(conn);
		if(stmt!=null)
		{
			try {
				ResultSet rs = stmt.executeQuery("USE " + DATABASE_NAME);
				rs = stmt.executeQuery("SELECT * FROM " + TABLE_NAME);
				while(rs.next())
				{
					Object[] row = new Object[]{rs.getString("ip_addr"), 
							rs.getString("username"), rs.getInt("status")};
					userlist.add(row);
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}		
		
		closeConnectionAndStatement(conn, stmt);
		return userlist;
	}
	
	/**
	 * @param ip_addr - String
	 * @param username - String of name to be displayed
	 * 
	 * This inserts a row into the MySQL database w/ the initial status of being free
	 */
	public static void login(String ip_addr, String username) {
		Connection conn = getConnection();
		Statement stmt = getStatment(conn);
		if(stmt!=null)
		{
			try {
				stmt.executeQuery("USE " + DATABASE_NAME);
				String insert_update = "INSERT INTO " + TABLE_NAME + " ("
					+ IP_ADDR_COLUMN_NAME + ", " 
					+ USERNAME_COLUMN_NAME + ", "
					+ STATUS_COLUMN_NAME 
					+ ") VALUES (" 
					+ "'" + ip_addr + "', "
					+ "'" + username + "', "
					+ STATUS_FREE
					+ ")";
				stmt.executeUpdate(insert_update);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}		
		closeConnectionAndStatement(conn, stmt);
	}
	
	/**
	 * @param ip_addr - String
	 * @param username - String of name to be displayed
	 * 
	 * This updates the status for the user w/ ip_addr
	 */
	public static void updateStatus(String ip_addr, int status) {
		Connection conn = getConnection();
		Statement stmt = getStatment(conn);
		if(stmt!=null)
		{
			try {
				stmt.executeQuery("USE " + DATABASE_NAME);
				String status_update = "UPDATE " + TABLE_NAME + " SET "
					+ STATUS_COLUMN_NAME + "=" + status
					+ " WHERE " + IP_ADDR_COLUMN_NAME + "='" + ip_addr + "'";
				stmt.executeUpdate(status_update);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}		
		closeConnectionAndStatement(conn, stmt);
	}
	
	/**
	 * @param ip_addr - String
	 * 
	 * This removes the user w/ ip_addr from the MySQL table
	 */
	public static void logout(String ip_addr) {
		Connection conn = getConnection();
		Statement stmt = getStatment(conn);
		if(stmt!=null)
		{
			try {
				stmt.executeQuery("USE " + DATABASE_NAME);
				String delete_update = "DELETE FROM " + TABLE_NAME + " WHERE "
					+ IP_ADDR_COLUMN_NAME + "=" 
					+ "'" + ip_addr + "'";
				stmt.executeUpdate(delete_update);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}		
		closeConnectionAndStatement(conn, stmt);
	}

	public static void main(String[] args) {
		long start = System.currentTimeMillis();
		login("192.168.0.1", "Kevin");
		getCurrentUsers();
		updateStatus("192.168.0.1", 3);
		getCurrentUsers();
		logout("192.168.0.1");
		getCurrentUsers();
		long end = System.currentTimeMillis();
		long delta = end - start;
		System.out.println(delta);
	}

}
